package cn.bytecollege.dao;

import cn.bytecollege.entity.UserManage;

import java.time.LocalDateTime;
import java.time.ZoneId;
import java.util.List;
import java.util.Date;

public class UserMessageDao extends BaseDao<UserManage,Integer> {
    /**
     * 查询所有已入住用户
     * zxy
     */
    public List<UserManage> findListByPage(int pageIndex, int pageSize, int userState){
        String sql="SELECT order_id,username,user_id_number,user_phone_number,user_start_time,is_vip,user_manage.room_id,true_price,user_state,room_number,room.room_category_id,room_category_name" +
                " FROM USER_MANAGE LEFT JOIN ROOM ON USER_MANAGE.ROOM_ID=ROOM.ROOM_ID LEFT JOIN ROOM_CATEGORY ON ROOM.ROOM_CATEGORY_ID=ROOM_CATEGORY.ROOM_CATEGORY_ID WHERE USER_STATE=? LIMIT ?,?;";
        int index=(pageIndex-1)*pageSize;
        Object[] objects={userState,index,pageSize};
        return helper.executeQuery(sql,UserManage.class,objects);
    }
    /**
     * 统计已入住用户总数
     * zxy
     */
    public int count(){
        String sql="SELECT COUNT(*) FROM USER_MANAGE WHERE USER_STATE=1";
        return helper.executeScale(sql);
    }
    /**
     * 更改住户入住状态以及房间入住状态
     * zxy
     */
    public int update(int id) {
        String sql="UPDATE USER_MANAGE LEFT JOIN ROOM ON ROOM.ROOM_ID=USER_MANAGE.ROOM_ID SET USER_STATE=0,ROOM_STATE=0,USER_END_TIME=? WHERE ORDER_ID=?;";
        return helper.executeUpdate(sql, LocalDateTime.now(),id);
    }
    /**
     * @author LH
     * 将住户信息添加到数据库
     * @param userManage
     * @return
     */
    @Override
    public int save(UserManage userManage) {
        String sql="INSERT INTO user_manage(username,user_id_number,user_phone_number,user_start_time,is_vip,room_id,true_price,user_state,user_end_time) VALUES(?,?,?,?,?,?,?,?,?)";
        Date date=new Date(0000,0,1,00,-5,-43);
        LocalDateTime localDateTime=date.toInstant().atZone(ZoneId.systemDefault()).toLocalDateTime();
        Object[] objects={userManage.getUsername(),userManage.getUserIdNumber(),userManage.getUserPhoneNumber(),userManage.getUserStartTime(),userManage.getIsVip(),userManage.getRoomId(),userManage.getTruePrice(),userManage.getUserState(),localDateTime};
        return helper.executeUpdate(sql,objects);
    }

    /**
     * @author LH
     *根据订单id 查询用户信息
     * @return
     */
    public UserManage findById(Integer userMessageId) {
        String sql ="SELECT u.order_id,username,user_phone_number,user_id_number, u.room_id,r.room_number FROM user_manage u JOIN room r on r.room_id=u.room_id where u.order_id=?";
        return helper.executeQuery(sql,UserManage.class,userMessageId).get(0);
    }

    /**
     * 修改住户信息
     * @param userManage
     * @return
     */
    public int change(UserManage userManage){
        String sql="UPDATE `hotel`.`user_manage` SET `username` = ?, `user_id_number` = ?, `user_phone_number` = ?, `user_start_time` = ?, `is_vip` =?, `room_id` = ?, `true_price` = ?, `user_state` = ? ,`user_end_time` = ? WHERE `order_id` = ?";
        Date date=new Date(0000,0,1,00,-5,-43);
        LocalDateTime localDateTime=date.toInstant().atZone(ZoneId.systemDefault()).toLocalDateTime();
        Object[] objects={userManage.getUsername(),userManage.getUserIdNumber(),userManage.getUserPhoneNumber(),userManage.getUserStartTime(),userManage.getIsVip(),userManage.getRoomId(),userManage.getTruePrice(),userManage.getUserState(),localDateTime,userManage.getOrderId()};
        return helper.executeUpdate(sql,objects);
    }

    /**
     * 查询已入住的用户数
     * @return
     */
    public int countByState(){
        String sql="select count(*) from user_manage where user_state=1";
        return  helper.executeScale(sql);
    }
}
